<?php defined('SYSPATH') or die('No direct script access.');
/**
 *
 *
 * @author yuqing
 *
 */
class Controller_Report_outlandonway extends Controller_Admin{
    /**
     * (non-PHPdoc)
     * @see Controller_Admin::before()
     */
//    public function before()
//    {
//        try {
//            ACL::Required('onway');
//        } catch ( HTTP_Exception_403 $e ) {
//            Message::error('sorry，你没有权限进这个目录');
//            $this->request->redirect ( URL::base (true) );
//        }
//        parent::before();
//    }

    /**
     * 首页
     */
    public function action_index()
    {
        $this->title='分仓库存销量查询';
        $view=View::factory('report/outlandonway');
        if(isset($_GET['errors'])){
            $view->set('errors',$_GET['errors']);
        }
        $this->response->body($view);
    }
    /**
     * 下载模板
     */
    public function action_down_template()
    {
        $this->title='下载模版';
        require_once(Kohana::find_file('vendor', 'PHPExcel/PHPExcel/IOFactory'));
        $this->objReader  = PHPExcel_IOFactory::createReader('Excel5');
        $objPHPExcel      = new PHPExcel();
        $objPHPExcel->getProperties()->setCreator("newerp")
            ->setLastModifiedBy("newerp")
            ->setTitle("Office 2003 XLSX Test Document")
            ->setSubject("Office 2003 XLSX Test Document")
            ->setDescription("Test document for Office 2003 XLSX, generated using PHP classes.")
            ->setKeywords("office 2003 openxml php")
            ->setCategory("Test result file");
        $objActSheet =$objPHPExcel->setActiveSheetIndex(0);
        $objActSheet->setCellValue('A1', 'SKU');
        $objPHPExcel->getActiveSheet()->setTitle('load');
        $objPHPExcel->setActiveSheetIndex(0);
        $day=date("m-d");
        ob_end_clean();
        header("Content-Type: application/vnd.ms-excel; charset=utf-8");
        header('Content-Disposition: attachment;filename="'.$day.'在途库存.xls"');
        header('Cache-Control: max-age=0');
        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $objWriter->save('php://output');
        exit;
    }
    /**
     * 导入sku查询
     */
    public function action_load()
    {
        set_time_limit(0);
        ini_set('memory_limit','1152M');
        ini_set('max_allowed_packet','33M');
        require_once(Kohana::find_file('vendor', 'PHPExcel/PHPExcel/IOFactory'));
        $xls= $_FILES['xls']['tmp_name'];
        $objPHPExcel= PHPExcel_IOFactory::load($xls);
        $data= $objPHPExcel->getActiveSheet()->toArray(null,true,true,true);
        $num = count($data);
        $error_arr=array();//存储不合格的行
        $begin_time=time();
        $skus_execl=$skus_arr=$error_num=$sku_error_arr=array();

        $sku_str='';
        for($i=2;$i<=$num;$i++){
            $skus_execl[$i-2]= strtoupper(trim($data[$i]['A']));
            $sku_str.=$sku_str==''?"'".strtoupper(trim($data[$i]['A']))."'":",'".strtoupper(trim($data[$i]['A']))."'";
        }
        if($sku_str!=''){
            //START     ----------------------------------CWY更新代码-------------------------------------
            $skus_arr = Skusystemapi::getSkuIn($sku_str);

            if(!empty($skus_arr)){
                $values = array();

                foreach ($skus_arr as $row)
                {
                    if (isset($row['sku']) && !empty($row['sku']))
                    {
                        $values[] = strtoupper($row['sku']);
                    }
                }

                $skus_arr =  $values;
                $sku_error_arr = array_diff($skus_execl,$skus_arr);
                if($sku_error_arr)
                {
                    $error_num = array_keys($sku_error_arr);
                }
            }
        }

        $sql = "SELECT pd.`sku` , pd.`sku_name` , p.`store` , SUM( pd.`quantity` - pd.`ware_quantity` ) AS num
                FROM `purchaseorder_details` pd
                JOIN `purchaseorders` p ON (pd.`status` < 70 AND p.`id` = pd.`purchaseorder_id`
                AND p.`status` != 100)
                AND `sku`
                IN (".$sku_str.") GROUP BY pd.`sku` , p.`store` " ;

        $data  = DB::query(Database::SELECT,$sql)->execute('newerp')->as_array();
        $stores = $this->getOutland();
        //构建excel数据格式
        $excelData = array();
        foreach($data as $v){
            if(!isset($excelData[$v['sku']])){
                $excelData[$v['sku']]['sku_name'] = $v['sku_name'];
                $excelData[$v['sku']][$v['store']] = $v['num'];
            }else{
                $excelData[$v['sku']][$v['store']] = $v['num'];
            }
        }
        $objPHPExcel      = new PHPExcel();
        $objPHPExcel->getProperties()->setCreator("YKS")
            ->setLastModifiedBy("YKS")
            ->setTitle("Office 2003 XLSX Test Document")
            ->setSubject("Office 2003 XLSX Test Document")
            ->setDescription("Test document for Office 2003 XLSX, generated using PHP classes.")
            ->setKeywords("office 2003 openxml php")
            ->setCategory("Test result file");
        $objActSheet =$objPHPExcel->setActiveSheetIndex(0);
        $objActSheet->setCellValue('A1', 'SKU');
        $objActSheet->setCellValue('B1', 'sku名称');
        $objActSheet->setCellValue('C1', '国内仓');
        $objActSheet->setCellValue('D1', $stores[1]);
        $objActSheet->setCellValue('E1', $stores[2]);
        $objActSheet->setCellValue('F1', $stores[3]);
        $objActSheet->setCellValue('G1', $stores[4]);
        $objActSheet->setCellValue('H1', $stores[5]);
        $objActSheet->setCellValue('I1', $stores[6] ? $stores[6]: '');
            $i =2;
        foreach($excelData as $k=>$val){
            $objActSheet->setCellValue('A'.$i,$k);
            $objActSheet->setCellValue('B'.$i,$val['sku_name']);
            $objActSheet->setCellValue('C'.$i,isset($val[0]) ? $val[0] : 0);
            $objActSheet->setCellValue('D'.$i,isset($val[1]) ? $val[1] : 0);
            $objActSheet->setCellValue('E'.$i,isset($val[2]) ? $val[2] : 0);
            $objActSheet->setCellValue('F'.$i,isset($val[3]) ? $val[3] : 0);
            $objActSheet->setCellValue('G'.$i,isset($val[4]) ? $val[4] : 0);
            $objActSheet->setCellValue('H'.$i,isset($val[5]) ? $val[5] : 0);
            $objActSheet->setCellValue('I'.$i,isset($val[6]) ? $val[6] : '');
            $i++;
        }

        if($sku_error_arr){
            $error_num=0;
            $numbers=count($skus_arr)+2;
            $objActSheet->setCellValue('A'.$numbers, '以下SKU找不到或者找不到数据');
            $error_num++;
            foreach($sku_error_arr as $_sku){
                $objActSheet->setCellValue('A'.($numbers+$error_num), $_sku);
                $error_num++;
            }
        }
        $objPHPExcel->getActiveSheet()->setTitle('分仓在途库存报表');
        $objPHPExcel->setActiveSheetIndex(0);
        $day=date("m-d");
        $filename=$day.'分仓在途库存报表'.'.xls';
        ob_end_clean();
        header("Content-Type: application/vnd.ms-excel; charset=utf-8");
        header('Content-Disposition: attachment;filename='.$filename);
        header('Cache-Control: max-age=0');
        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $objWriter->save('php://output');
        exit;

    }
    function getOutland(){
        return DB::query(Database::SELECT, 'SELECT `di_value`,`di_caption` FROM `myr_dd_item` WHERE `dd_id` = 19')
            ->execute('outland')->as_array('di_value', 'di_caption');
    }
}